package ut.data;
import ut.res.MessageType;
import java.util.Date;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
public class Discussion{
	public static boolean createDiscussion(long taskId, User user, String content){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			int employer, translator;
			rs = stmt.executeQuery("select employer,translator from task where id=" + taskId + " and state!=" + Task.MATCHING);
			if(rs.next()){
				employer = rs.getInt("employer");
				translator = rs.getInt("translator");
			}else{
				return false;
			}
			stmt.executeUpdate("insert into discussion(task,user,content,time) values(" + taskId + "," + user.id + ",'" + content.replace("'", "''") + "',now())");
			con.commit();
			// 若資料處理成功，則寄送訊息給相關使用者
			if(user.id == employer){ // 案主張貼討論訊息
				MessageType messageType = MessageType.getMessageType(MessageType.DISCUSSION_EMPLOYER);
				ut.data.Message.createMessage(translator, MessageType.getFilledContent(messageType, new String[]{user.name, taskId + ""}), messageType);
			}else if(user.id == translator){ // 譯者張貼討論訊息
				MessageType messageType = MessageType.getMessageType(MessageType.DISCUSSION_TRANSLATOR);
				ut.data.Message.createMessage(employer, MessageType.getFilledContent(messageType, new String[]{user.name, taskId + ""}), messageType);
			}
			return true;
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	public static int getMaxDiscussionPage(long taskId, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from discussion where task=" + taskId);
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	public static Discussion[] getDiscussions(long taskId, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Discussion[] discussions = new Discussion[pageSize];
			rs = stmt.executeQuery("select * from discussion where task=" + taskId + " order by time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				discussions[index] = new Discussion(rs.getLong("id"), rs.getLong("task"), rs.getInt("user"),
					rs.getString("content"), rs.getTimestamp("time"));
				index++;
			}
			return discussions;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	/* Instance Definition */
	public long id;
	public long task;
	public int user;
	public String content;
	public Date time;
	public Discussion(long id, long task, int user, String content, Date time){
		this.id = id;
		this.task = task;
		this.user = user;
		this.content = content;
		this.time = time;
	}
}